package cn.edu.zjicm.wordsnet_d.db;

import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Vector;

import cn.edu.zjicm.wordsnet_d.data.OneWord;
import cn.edu.zjicm.wordsnet_d.datapersistent.AppPreference;
import cn.edu.zjicm.wordsnet_d.util.LogUtil;
import android.content.Context;
import android.database.Cursor;
import net.sqlcipher.database.SQLiteDatabase;
import net.sqlcipher.database.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	public static final String PACKAGE_NAME = "cn.edu.zjicm.wordsnet_d";// 包名
	public static final String DB_PATH = "/data" + Environment.getDataDirectory().getAbsolutePath() + "/" + PACKAGE_NAME + "/databases/";
	public static final String DATABASE_NAME = "wordsrec.s3db";
	static final int DATABASE_VERSION = 17;  //已修改--20150602 v2.11.0
	static List<SQLiteDatabase> dbpool = new Vector<SQLiteDatabase>();
	public static boolean dbinited = true;
	private Context context;

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		Locale.setDefault(Locale.CHINA);
		this.context = context;
	}

	@Override
	// 创建数据库后，对数据库的操作
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	// 更改数据库版本的操作
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.e("start db update", "fome " + oldVersion + " to " + newVersion);
		AppPreference.setOldDbVersion(context, oldVersion); // 存储数据库旧版本号

		if (oldVersion < 3) { // 将我的单词本中词义熟悉度为6、7的生词全部改为5
			String sql = "update " + WordFactory.TABLE_WORDS + " set sense_degree_fm=5" + " where sense_degree_fm>5 and degree_fm="
					+ OneWord.DEGREE_UNFAMILIAR;
			db.execSQL(sql);
		}

		if (newVersion == DATABASE_VERSION) {
			if (oldVersion <= 3)
				WordFactory.exportTableWordsLogFromWords(db, context);
			else if (oldVersion >= 4)
				WordFactory.exportTableWordsLog(db, oldVersion, context);
			if (oldVersion >= 9) // 从数据库版本9开始有打卡表，需先导出，复制数据库后恢复
				WordFactory.exportTablePunchOutLog(db);
			if (oldVersion >= 11) { // 从数据库版本11开始有用户财富表(wealth)和订单信息表(order_log)，需先导出，复制数据库后恢复
				WordFactory.exportTableZMD(db);
				WordFactory.exportTableOrderLog(db);
			}
			AppPreference.setIsFirstUseThisVersion(context, true); // 设置第一次允许新版本为true，为了使其重新复制数据库
		}
		/*
		 * if (oldVersion == 1 && newVersion == 2) {
		 * updateDatabaseVersion1to2(db); } else if (oldVersion == 2 &&
		 * newVersion == 3) { updateDatabaseVersion2to3(db); } else if
		 * (oldVersion == 3 && newVersion == 4) { updateDatabaseVersion3to4(db);
		 * } else if (oldVersion == 1 && newVersion == 3) {
		 * updateDatabaseVersion1to2(db); updateDatabaseVersion2to3(db); } else
		 * if (oldVersion == 2 && newVersion == 4) {
		 * updateDatabaseVersion2to3(db); updateDatabaseVersion3to4(db); } else
		 * if (oldVersion == 1 && newVersion == 4) {
		 * updateDatabaseVersion1to2(db); updateDatabaseVersion2to3(db);
		 * updateDatabaseVersion3to4(db); }
		 */

		/*
		 * String table1 =
		 * "CREATE TABLE [test] ([id] INTEGER  PRIMARY KEY NOT NULL,[nodes] TEXT  NULL);"
		 * ; db.execSQL(table1); String sql =
		 * "INSERT INTO test VALUES(1,'test');"; db.execSQL(sql);
		 */
	}

	void dropTableWord(SQLiteDatabase db) {
		String sql = "drop table if exists words";
		try {
			db.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	// 每次成功打开数据库后首先被执行
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);

		dbpool.add(db);
	}

	public static void closeAll() {
		// TODO Auto-generated method stub
		Iterator<SQLiteDatabase> i = dbpool.iterator();
		while (i.hasNext())
			try {
				i.next().close();
			} catch (Exception e) {
			}
	}

	// 修正数据库搭配表的错误数据
	public void updateDatabaseVersion1to2(SQLiteDatabase db) {
		String str = "in dbhelper update from " + 1 + " to " + 2;
		Log.e("db update", str);
		// LogUtil.addLog("db update:" + str);
		String sql[] = {
				"update colloc set colloc_eng='accommodate 1,500 guests',colloc_cn='容纳1,500名客人',word_str='1024,0;2439,2' where node_id=54979",
				"update colloc set colloc_eng='fly at an altitude of 40,000 feet',colloc_cn='在4万英尺的高度飞行',word_str='100683,0;1098,3;201962,6' where node_id=11251",
				"update colloc set colloc_eng='a city of 100,000 inhabitants',colloc_cn='有十万居民的城市',word_str='2626,4' where node_id=306761",
				"update colloc set colloc_eng='Mechanics is out of my territory.',colloc_cn='我不懂力学。',word_str='2988,0;4349,5' where node_id=56552",
				"update colloc set colloc_eng='throw a pool of violet light on the stage',colloc_cn='在舞台上投洒下一团紫光',word_str='4372,0;203653,2;4609,4;202800,5;4154,8' where node_id=66292",
				"update colloc set colloc_eng='sails swelling out in the wind',colloc_cn='迎风鼓起的船帆',word_str='3876,0;4294,1;102053,5' where node_id=72056",
				"update colloc set colloc_eng='relieve (oneself) of troublesome thoughts',colloc_cn='吐露烦恼',word_str='3704,0;4464,3;204968,4' where node_id=318002" };
		try {
			for (int i = 0; i < sql.length; i++)
				db.execSQL(sql[i]);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 将我的单词本中词义熟悉度为6、7的生词全部改为5,words表增加time_lock字段
	public void updateDatabaseVersion2to3(SQLiteDatabase db) {
		String str = "in dbhelper update from " + 2 + " to " + 3;
		Log.e("db update", str);
		// LogUtil.addLog("db update:" + str);
		String sql[] = {
				"update " + WordFactory.TABLE_WORDS + " set sense_degree_fm=5" + " where sense_degree_fm>5 and degree_fm="
						+ OneWord.DEGREE_UNFAMILIAR, "alter TABLE words add time_lock INTEGER DEFAULT '0' NULL" };
		try {
			for (int i = 0; i < sql.length; i++)
				db.execSQL(sql[i]);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 创建表words_log
	public void updateDatabaseVersion3to4(SQLiteDatabase db) {
		String str = "in dbhelper update from " + 3 + " to " + 4;

		// 如果数据库中words表中没有time_lock字段，说明数据库从2－3版的升级没有做过，这里再做一遍
		if (!hasTimeLockInWords(db)) {
			updateDatabaseVersion2to3(db);
		}
		creatWordsLogTable(db);
		dropColumnsInWords(db);
		createIndexs(db);
		AppPreference.setIsFirstUseThisVersion(context, true); // 设置第一次允许新版本为true，为了使其更新单词熟悉度统计
	}

	// 判断表words中是否有time_lock字段
	public boolean hasTimeLockInWords(SQLiteDatabase db) {
		boolean result = true;
		Cursor cursor = db.rawQuery("select * from words LIMIT 0", null);
		if (cursor.getColumnIndex("time_lock") == -1) {
			result = false;
		}
		cursor.close();
		return result;
	}

	// 数据中创建学习纪录表
	public void creatWordsLogTable(SQLiteDatabase db) {

		// 创建新表words_log
		String sql = "CREATE TABLE [words_log] (" + "[index_word_id] INTEGER  PRIMARY KEY NOT NULL," + "[time_set] INTEGER DEFAULT '0' NOT NULL,"
				+ "[degree_fm] INTEGER DEFAULT '0' NOT NULL," + "[voice_degree_fm] INTEGER DEFAULT '0' NULL,"
				+ "[last_huanbo_time] INTEGER DEFAULT '0' NULL," + "[sense_degree_fm] INTEGER DEFAULT '0' NULL,"
				+ "[last_test_time] INTEGER DEFAULT '0' NULL," + "[time_forget] INTEGER DEFAULT '0' NULL," + "[time_lock] INTEGER DEFAULT '0' NULL)";
		// 将表words中熟悉度为>0的纪录添加至words_log
		String columns = "index_word_id,time_set,degree_fm,voice_degree_fm," + "last_huanbo_time,sense_degree_fm,last_test_time,"
				+ "time_forget,time_lock";
		String sql1 = "INSERT INTO words_log(" + columns + ") SELECT " + columns + " FROM words where degree_fm>0";

		/*
		 * //从表words分离出表words_log String sql =
		 * "create table words_log as select " +
		 * "index_word_id,time_set,degree_fm,voice_degree_fm,last_huanbo_time,"
		 * + "sense_degree_fm,last_test_time,time_forget,time_lock " +
		 * "from words"; //删除表words_log中熟悉度为0的纪录 String sql1 =
		 * "delete from words_log where degree_fm=0";
		 */
		try {
			db.execSQL(sql);
			db.execSQL(sql1);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 删除words表中的部分字段(已迁移到words_log)
	public void dropColumnsInWords(SQLiteDatabase db) {
		// String sql = "alter table words drop column "
		// +
		// "index_word_id,time_set,degree_fm,voice_degree_fm,last_huanbo_time,"
		// + "sense_degree_fm,last_test_time,time_forget,time_lock";
		String sql = "create table words_temp as select " + "index_word_id,lemma,senses_senior,senses_sch,phonetic," + "aff_content,haveaudio "
				+ "from words";
		String sql1 = "drop table if exists words";
		String sql2 = "alter table words_temp rename to words";
		try {
			db.execSQL(sql);
			db.execSQL(sql1);
			db.execSQL(sql2);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void createIndexs(SQLiteDatabase db) {
		String sqls[] = { "CREATE UNIQUE INDEX [IDX_WORDS_] ON [words]([index_word_id]  DESC)",
				"CREATE UNIQUE INDEX [IDX_WORDS_LEVELS_] ON [words_levels]" + "([index_word_id]  DESC,[book_id]  DESC,[ind]  DESC)",
				"CREATE UNIQUE INDEX [IDX_WORDS_LOG_] ON [words_log]([index_word_id]  DESC)",
				"CREATE UNIQUE INDEX [IDX_COLLOC_] ON [colloc]" + "([index_word_id]  DESC,[node_id]  DESC,[node_index]  DESC)" };
		try {
			for (int i = 0; i < sqls.length; i++)
				db.execSQL(sqls[i]);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/*
	 * void exportTableWordsLog(SQLiteDatabase db){ String fileName =
	 * Environment.getExternalStorageDirectory().toString() +
	 * "/iwordnet/words_log.txt"; // 打开一个写文件器，构造函数中的第二个参数true表示以追加形式写文件 try {
	 * FileWriter writer = new FileWriter(fileName, false);
	 * 
	 * Cursor c; String sql = "select * from words where degree_fm>0";
	 * 
	 * c = db.rawQuery(sql, null); while (c.moveToNext()) {
	 * writer.write("insert into words_log values (" + c.getInt(0) + "," +
	 * c.getInt(7) + "," + c.getInt(8) + "," + c.getInt(9) + "," + c.getInt(10)
	 * + "," + c.getInt(11) + "," + c.getInt(12) + "," + c.getInt(13) + ",0" +
	 * ")" + "\n"); } c.close(); writer.close(); } catch (IOException e) {
	 * e.printStackTrace(); } }
	 */
}
